

/*

PROMPT CREATE INDEX se_art_genechapatt_03
CREATE INDEX se_art_genechapatt_03
  ON se_art_genechapatt (
    gene_cod,
    gene_ind,
    chap_cod,
    vrt_cod,
    vrtattatt_cod
  )
  NOLOGGING
;


*/

define erp='erp_mli2.';
define outils='outils_mli2.';

/*
                      SELECT * FROM export_art_comp_variant_prblm WHERE  combsto_id =33213
                      SELECT * FROM export_art_comp WHERE "ART_COMP.COMBSTO_ID" =33213
                      SELECT * FROM export_art_comp_doublons WHERE comb =33213
                      select * from temp_se_VRT_COMBSTOVAL where combsto_id=8862
                      select * from uv_pproduits where numspecif=33213
                      select * from uv_pproduits_statut_actuel where numspecif=7950

*/
/*creation d'une table de travail temporaire*/
drop TABLE temp_se_VRT_COMBSTOVAL;

CREATE TABLE temp_se_VRT_COMBSTOVAL as
 (SELECT att.id,
         vrt.VARIANTINT vrt_cod,
         att.listatt+0 att_cod,
         val.CONTATT combattval_val,
         CAST(SubStr(p.prodbase,1,Length(p.prodbase)-1) AS varchar2(15)) gene_cod,
         CAST(SubStr(p.prodbase,Length(p.prodbase),2) AS varchar2(2)) gene_ind ,
         CAST(p.numspecif AS NUMBER (20,0))  combsto_id,
         CAST(att.rangmulti2*20+att.rangmulti3 AS NUMBER (4,0)) combstoval_rang ,
         CAST(null AS NUMBER (20,0)) combattval_num ,
         CAST(null AS varchar2(1)) flag,
         tv.typevar
    FROM UV_PPRODUITS_LISTATT att ,UV_PPRODUITS_VARIANTINT vrt,UV_PPRODUITS_CONTATT val, uv_pproduits p,uv_pproduits_statut_actuel s
        ,uv_pproduits_typevar tv /*decode(tv.typevar,'S','N','C','O','O','N','O') formvrt_ok*/
    WHERE vrt.id=att.id  AND vrt.RANGMULTI2 =att.RANGMULTI2
      AND tv.id(+)=vrt.id AND tv.RANGMULTI2(+)=vrt.RANGMULTI2
      AND p.id=vrt.id
      AND s.id=p.id AND (s.statut<900 OR s.statut IS NULL)
      AND val.id(+)=att.id  AND val.RANGMULTI2(+) =att.RANGMULTI2 AND val.RANGMULTI3(+)=att.RANGMULTI3
      AND att.listatt IS NOT NULL
      AND p.codgestion=3
      -- AND val.CONTATT IS NOT NULL
      -- AND p.id LIKE '%01925924'
      -- AND tv.typevar='C'
      AND vrt.VARIANTINT IS NOT NULL --and att.id LIKE '%00000127'
     );

  /*SELECT * FROM  temp_se_VRT_COMBSTOVAL WHERE combsto_id=4130*/


--PROMPT CREATE INDEX temp_se_VRT_COMBSTOVAL_02
CREATE INDEX temp_se_VRT_COMBSTOVAL_02
  ON temp_se_VRT_COMBSTOVAL (
    id
  )
  NOLOGGING
;

--PROMPT CREATE INDEX temp_se_VRT_COMBSTOVAL_01
CREATE INDEX temp_se_VRT_COMBSTOVAL_01
  ON temp_se_VRT_COMBSTOVAL (
    gene_cod,
    gene_ind,
    vrt_cod,
    att_cod
  )
  NOLOGGING
;




UPDATE temp_se_VRT_COMBSTOVAL t set flag='Z' WHERE EXISTS ( SELECT 1 FROM
    (SELECT combsto_id , Count(DISTINCT combattval_val) nbval,Count(1) nbatt
    FROM temp_se_VRT_COMBSTOVAL v
    GROUP BY combsto_id HAVING Count(DISTINCT combattval_val)=0) r WHERE r.combsto_id=t.combsto_id) ;


/*suppression des points ..... inutiles que si ca ne genere pas de combinaison identique*/

UPDATE temp_se_VRT_COMBSTOVAL t SET COMBATTVAL_VAL = REPLACE(COMBATTVAL_VAL,' ','¤') WHERE COMBATTVAL_VAL LIKE '% %'
  AND EXISTS (SELECT 1 FROM &erp.art_genechapatt a,&erp.gen_champctav c
              WHERE a.CHAMPSAIS_COD=c.CHAMPSAIS_COD AND (c.GRILLE_COD IS NOT NULL OR c.ENTITEENT_COD IS NOT null or a.vrt_cod='LON' or a.vrt_cod='TAI' or a.vrt_cod='THS' or a.vrt_cod='THB')
                AND t.gene_cod=a.gene_cod AND t.gene_ind=a.gene_ind AND t.vrt_cod=a.vrt_cod and t.att_cod=a.vrtattatt_cod) ;
UPDATE temp_se_VRT_COMBSTOVAL t SET COMBATTVAL_VAL = REPLACE(COMBATTVAL_VAL,'.',' ') WHERE COMBATTVAL_VAL LIKE '%.%'
  AND EXISTS (SELECT 1 FROM &erp.art_genechapatt a,&erp.gen_champctav c
              WHERE a.CHAMPSAIS_COD=c.CHAMPSAIS_COD AND (c.GRILLE_COD IS NOT NULL OR c.ENTITEENT_COD IS NOT null or a.vrt_cod='LON' or a.vrt_cod='TAI' or a.vrt_cod='THS' or a.vrt_cod='THB')
                AND t.gene_cod=a.gene_cod AND t.gene_ind=a.gene_ind AND t.vrt_cod=a.vrt_cod and t.att_cod=a.vrtattatt_cod) ;
UPDATE temp_se_VRT_COMBSTOVAL t SET COMBATTVAL_VAL = trim(COMBATTVAL_VAL)
 WHERE COMBATTVAL_VAL LIKE '% %' AND Length(trim(COMBATTVAL_VAL))>0
   AND EXISTS (SELECT 1 FROM &erp.art_genechapatt a,&erp.gen_champctav c
              WHERE a.CHAMPSAIS_COD=c.CHAMPSAIS_COD AND (c.GRILLE_COD IS NOT NULL OR c.ENTITEENT_COD IS NOT null or a.vrt_cod='LON' or a.vrt_cod='TAI' or a.vrt_cod='THS' or a.vrt_cod='THB')
                AND t.gene_cod=a.gene_cod AND t.gene_ind=a.gene_ind AND t.vrt_cod=a.vrt_cod and t.att_cod=a.vrtattatt_cod) ;
UPDATE temp_se_VRT_COMBSTOVAL t SET COMBATTVAL_VAL = REPLACE(COMBATTVAL_VAL,' ','.') WHERE COMBATTVAL_VAL LIKE '% %'
  AND EXISTS (SELECT 1 FROM &erp.art_genechapatt a,&erp.gen_champctav c
              WHERE a.CHAMPSAIS_COD=c.CHAMPSAIS_COD AND (c.GRILLE_COD IS NOT NULL OR c.ENTITEENT_COD IS NOT null or a.vrt_cod='LON' or a.vrt_cod='TAI' or a.vrt_cod='THS' or a.vrt_cod='THB')
                AND t.gene_cod=a.gene_cod AND t.gene_ind=a.gene_ind AND t.vrt_cod=a.vrt_cod and t.att_cod=a.vrtattatt_cod) ;
UPDATE temp_se_VRT_COMBSTOVAL t SET COMBATTVAL_VAL = REPLACE(COMBATTVAL_VAL,'¤',' ') WHERE COMBATTVAL_VAL LIKE '%¤%'
  AND EXISTS (SELECT 1 FROM &erp.art_genechapatt a,&erp.gen_champctav c
              WHERE a.CHAMPSAIS_COD=c.CHAMPSAIS_COD AND (c.GRILLE_COD IS NOT NULL OR c.ENTITEENT_COD IS NOT null or a.vrt_cod='LON' or a.vrt_cod='TAI' or a.vrt_cod='THS' or a.vrt_cod='THB')
                AND t.gene_cod=a.gene_cod AND t.gene_ind=a.gene_ind AND t.vrt_cod=a.vrt_cod and t.att_cod=a.vrtattatt_cod) ;
UPDATE temp_se_VRT_COMBSTOVAL t SET COMBATTVAL_VAL = trim(COMBATTVAL_VAL)
 WHERE COMBATTVAL_VAL LIKE '% %' AND Length(trim(COMBATTVAL_VAL))>0
   AND EXISTS (SELECT 1 FROM &erp.art_genechapatt a,&erp.gen_champctav c
              WHERE a.CHAMPSAIS_COD=c.CHAMPSAIS_COD AND (c.GRILLE_COD IS NOT NULL OR c.ENTITEENT_COD IS NOT null or a.vrt_cod='LON' or a.vrt_cod='TAI' or a.vrt_cod='THS' or a.vrt_cod='THB')
                AND t.gene_cod=a.gene_cod AND t.gene_ind=a.gene_ind AND t.vrt_cod=a.vrt_cod and t.att_cod=a.vrtattatt_cod) ;


UPDATE &erp.for_identifiant i SET ID1_cod='9999'
WHERE fortype_cod ='ATTVRT' AND id1_cod='REPR_UV' ;

UPDATE &erp.gen_champsais c SET FORMAT_VAL=null
where format_val  IS NOT NULL
  AND  EXISTS (SELECT 1 FROM &erp.art_genechapatt a WHERE a.CHAMPSAIS_COD=c.CHAMPSAIS_COD) ;

/*maj de la combsto*/
/*UPDATE temp_se_VRT_COMBSTOVAL tf SET COMBSTO_ID=(SELECT NUMSPECIF FROM uv_pproduits p WHERE tf.id=p.id AND codgestion = 3);*/
/*maj du generique et indice*/
/*UPDATE temp_se_VRT_COMBSTOVAL tf SET (gene_cod,gene_ind)=(SELECT SubStr(prodbase,1,Length(prodbase)-1),SubStr(prodbase,Length(prodbase),2) FROM uv_pproduits p WHERE tf.id=p.id)
WHERE ROWNUM <2000 AND tf.gene_cod IS null;*/
/*maj rang*/
/*UPDATE temp_se_VRT_COMBSTOVAL tf SET COMBSTOVAL_RANG=(SELECT combinaison_rang FROM se_art_genechapatt art
                                                      WHERE art.gene_cod=tf.gene_cod AND art.gene_ind=tf.gene_ind
                                                        AND art.vrt_cod=tf.vrt_cod AND art.vrtattatt_cod=tf.att_cod
                                                        AND art.chap_cod='MLI')
                                WHERE EXISTS (SELECT COMBSTOVAL_RANG FROM temp_se_VRT_COMBSTOVAL art
                                              WHERE art.gene_cod=tf.gene_cod AND art.gene_ind=tf.gene_ind
                                                AND art.vrt_cod=tf.vrt_cod AND art.att_cod=tf.att_cod
                                                AND COMBSTOVAL_RANG IS NULL   )  ;*/

--UPDATE temp_se_VRT_COMBSTOVAL tf SET COMBATTVAL_VAL ='###' WHERE COMBATTVAL_VAL IS NULL ;

/*SELECT art.*,vart.*
FROM se_art_genechapatt vart,temp_se_VRT_COMBSTOVAL art
WHERE art.gene_cod=vart.gene_cod AND art.gene_ind=vart.gene_ind
  AND art.vrt_cod=vart.vrt_cod AND vart.chap_cod='MLI'
  AND vart.vrtattatt_cod(+)=art.att_cod
  AND art.combsto_id=512177
ORDER BY vart.combinaison_rang */


/*mise a jour de quelques valeurs de variantes*/
UPDATE temp_se_VRT_COMBSTOVAL a SET a.COMBATTVAL_VAL ='STD'
WHERE a.COMBATTVAL_VAL ='....' AND a.VRT_COD ='LON'
  AND EXISTS (SELECT 1 FROM &erp.art_genechapvrt aa
              WHERE aa.gene_cod=a.gene_cod AND aa.gene_ind=a.gene_ind AND aa.vrt_cod=a.vrt_cod
                AND aa.genechapvrtoblig_ok='O') ;
UPDATE temp_se_VRT_COMBSTOVAL SET COMBATTVAL_VAL ='SAM' WHERE COMBATTVAL_VAL ='S+M' AND vrt_cod='FBA' AND ATT_COD=1  ;
UPDATE temp_se_VRT_COMBSTOVAL SET COMBATTVAL_VAL ='BAX' WHERE COMBATTVAL_VAL ='BA+' AND vrt_cod='TPR' AND ATT_COD=2  ;
UPDATE temp_se_VRT_COMBSTOVAL SET COMBATTVAL_VAL ='KUN' WHERE COMBATTVAL_VAL ='K+N' AND vrt_cod='DM1' AND ATT_COD=1  ;
UPDATE temp_se_VRT_COMBSTOVAL SET COMBATTVAL_VAL ='KUN' WHERE COMBATTVAL_VAL ='K+N' AND vrt_cod='TVI' AND ATT_COD=4  ;

/*variante d'article ayant au moins un attribut de renseign\ufffd*/
DROP TABLE temp_se_VRT_COMBSTOVAL2;
CREATE TABLE temp_se_VRT_COMBSTOVAL2 AS ( SELECT DISTINCT  gene_cod,gene_ind,combsto_id,vrt_cod FROM temp_se_VRT_COMBSTOVAL WHERE combattval_val IS NOT NULL);
UPDATE temp_se_VRT_COMBSTOVAL t SET t.COMBATTVAL_VAL='###'
WHERE t.vrt_cod='THS' AND t.COMBATTVAL_VAL IS null
  AND EXISTS (SELECT 1 from temp_se_VRT_COMBSTOVAL2 tt WHERE tt.combsto_id=t.combsto_id AND tt.vrt_cod=t.vrt_cod);

UPDATE temp_se_VRT_COMBSTOVAL t SET t.COMBATTVAL_VAL='###'
WHERE t.vrt_cod='THB' AND t.COMBATTVAL_VAL IS null
  AND EXISTS (SELECT 1 from temp_se_VRT_COMBSTOVAL2 tt WHERE tt.combsto_id=t.combsto_id AND tt.vrt_cod=t.vrt_cod);



/* mise a jour de quelques indices d'article*/
UPDATE temp_se_VRT_COMBSTOVAL SET gene_ind ='0' WHERE gene_ind ='.' ;
COMMIT;


/*remplissage des grilles de vriante avec les valeurs manquantes*/
DELETE FROM se_vrt_grillestd;
INSERT INTO se_vrt_grillestd (
        SELECT distinct
                0 enreg_id,
                c.grille_cod grille_cod,
                v.combattval_val grillecle_cod,
                'N' enreg_s,
                'pour reprise complet stock' grillestd_lib,
                NULL grillestd_lib2,
                1 grillestd_rang,
                NULL regroup_cod,
                NULL poids_val
        FROM temp_se_VRT_COMBSTOVAL v, &erp.vrt_att a,&erp.gen_champctav c
        WHERE v.vrt_cod=a.vrt_cod AND v.att_cod=a.att_cod AND a.champsais_cod =c.champsais_cod
          AND c.grille_cod IS NOT NULL
          AND NOT EXISTS (SELECT * FROM &erp.vrt_grillestd g where g.grille_cod=c.grille_cod AND g.grillecle_cod =v.combattval_val)
          AND v.COMBATTVAL_VAL IS NOT NULL
          AND Length(v.combattval_val)<=30 ) ;
UPDATE se_vrt_grillestd SET enreg_id = &outils.enreg_id.NEXTVAL WHERE enreg_id=0 ;

call  uvse_synchroverserp ('GRILLE_COD@GRILLECLE_COD@','VRT_GRILLESTD','O')  ;



/*cr\ufffdation de la table de tous les combinaisons stock a traiter*/
DROP TABLE export_art_comp ;
CREATE TABLE export_art_comp AS (
SELECT "ART_COMP.GENE_COD","ART_COMP.GENE_IND","ART_COMP.COMBSTO_ID",LISTEVAR_LIB,'N' doublons,'N' variantmanque FROM (
      SELECT gene_cod AS "ART_COMP.GENE_COD",
            gene_ind AS "ART_COMP.GENE_IND",
            combsto_id AS "ART_COMP.COMBSTO_ID",
            row_number() over (partition by gene_cod,gene_ind,combsto_id order by gene_cod,gene_ind,combsto_id,combstoval_rang) rang,
            CASE WHEN row_number() over (partition by gene_cod,gene_ind,combsto_id order by gene_cod,gene_ind,combsto_id,combstoval_rang)=1
            THEN outils_mli2.stragg_all(outils_mli2.stragg_expr('MLI+'||vrt_cod||'+'||att_cod||'+'||COMBATTVAL_VAL,']')) over
              (partition by gene_cod,gene_ind,combsto_id order by gene_cod,gene_ind,combsto_id,combstoval_rang
                rows between unbounded preceding and unbounded following)
            END LISTEVAR_LIB
      FROM temp_se_VRT_COMBSTOVAL
      WHERE  combsto_id IS NOT NULL
        AND combattval_val IS not null
        AND flag IS null
)
WHERE rang=1)  ;


/*identification des variantes avec au moins 1 attribut de variante renseign\ufffd*/
DROP TABLE temp_se_VRT_COMBSTO_VRT_RENS;
CREATE TABLE temp_se_VRT_COMBSTO_VRT_RENS AS (
SELECT distinct t.id,t.gene_cod,t.gene_ind,t.combsto_id,t.vrt_cod vrt_renseignee
FROM temp_se_VRT_COMBSTOVAL t
WHERE t.combattval_val IS NOT NULL  );

CREATE INDEX temp_se_VRT_COMBSTO_VRT_RE_01
  ON temp_se_VRT_COMBSTO_VRT_RENS (
    id,
    vrt_renseignee
  )
  NOLOGGING
;

/*nombre d'attribut renseign\ufffd STO de variante diff\ufffdrent du g\ufffdn\ufffdrique */
DROP TABLE temp_se_VRT_COMBSTO_VRT_gen;
CREATE TABLE temp_se_VRT_COMBSTO_VRT_gen AS (
SELECT uv.id,uv.combsto_id,uv.gene_cod,uv.gene_ind,uv.vrt_cod,outils_mli2.stragg(uv.vrt_cod || ' ' || uv.nb_att_uv || ' attribut au lieu de ' || nb_att_se) probleme FROM
  (/*nb attribut par article et variante*/
  SELECT t.ID,t.combsto_id,t.GENE_COD,t.GENE_IND,t.VRT_COD,Count(*) nb_att_uv
  FROM temp_se_VRT_COMBSTOVAL t
  GROUP BY t.ID,t.combsto_id,t.GENE_COD,t.GENE_IND,t.VRT_COD) uv
  ,(/*nb attribut par article/variante */
  SELECT gene_cod,gene_ind,vrt_cod,Count(*) nb_att_se
  FROM &erp.art_genechapatt g
  WHERE g.GENECHAPATTSTO_OK='O'
  GROUP BY gene_cod,gene_ind,vrt_cod  ) se
WHERE se.gene_cod=uv.gene_cod
  AND se.gene_ind=uv.gene_ind
  AND se.vrt_cod=uv.vrt_cod
  AND uv.nb_att_uv!=se.nb_att_se

GROUP BY uv.id,uv.combsto_id,uv.gene_cod,uv.vrt_cod,uv.gene_ind ) ;

CREATE INDEX temp_se_VRT_COMBSTO_VRT_ge_01
  ON temp_se_VRT_COMBSTO_VRT_gen (
    id,
    vrt_cod
  )
  NOLOGGING
;


/*identification des combsto ou il manque des variantes obligatoire pour ne pas les envoyer vers SE*/
DROP TABLE export_art_comp_variant_prblm ;
CREATE TABLE export_art_comp_variant_prblm AS (
SELECT t.id,t.gene_cod,t.gene_ind,t.combsto_id,outils_mli2.stragg(t.vrt_cod || t.att_cod) variantes_oblig_sans_valeur,p.listevar_lib
FROM temp_se_VRT_COMBSTOVAL t, &erp.art_genechapatt a ,&erp.art_genechapvrt v, export_art_comp p
WHERE ((((NOT EXISTS (SELECT * FROM &erp.for_identifiant i where FORTYPE_COD ='VRT' AND i.ID3_COD =t.vrt_cod )
          AND v.genechapvrtoblig_ok='O')
         OR ( EXISTS (SELECT 1 FROM temp_se_VRT_COMBSTO_VRT_RENS tr WHERE tr.id=t.id AND tr.vrt_renseignee=t.vrt_cod)
        ))
        AND t.combattval_val IS NULL )
        OR ( EXISTS (SELECT 1 FROM temp_se_VRT_COMBSTO_VRT_gen tb WHERE tb.id=t.id AND tb.vrt_cod=t.vrt_cod)
        ))
  AND t.gene_cod=a.gene_cod
  AND t.gene_ind=a.gene_ind
  AND t.vrt_cod =a.vrt_cod
  AND t.att_cod=a.vrtattatt_cod
  AND a.GENECHAPATTSTO_OK='O'
  AND t.gene_cod=v.gene_cod
  AND t.gene_ind=v.gene_ind
  AND t.vrt_cod =v.vrt_cod
  --AND t.TYPEVAR !='C'
  AND "ART_COMP.COMBSTO_ID"(+)=t.combsto_id
  --AND t.combsto_id =1966586
GROUP BY t.id,t.gene_cod,t.gene_ind,t.combsto_id ,p.listevar_lib ) ;


/*identification des doublons pour ne pas les envoyer vers SE*/
DROP TABLE export_art_comp_doublons ;
CREATE TABLE export_art_comp_doublons AS
(SELECT * FROM (
  SELECT SubStr(combs , InStr(','||combs||',',',',1,x+1), (InStr(','||combs||',',',',1,x+2))-(InStr(','||combs||',',',',1,x+1))-1) comb,combs
        ,gene_cod,gene_ind, LISTEVAR_LIB
  from
    (SELECT &outils.stragg("ART_COMP.COMBSTO_ID") combs,"ART_COMP.GENE_COD" gene_cod,"ART_COMP.GENE_IND" gene_ind,"LISTEVAR_LIB"
    FROM export_art_comp
    GROUP BY "ART_COMP.GENE_COD","ART_COMP.GENE_IND","LISTEVAR_LIB"
    HAVING Count(*) >1 ) req
    ,&outils.pivot p
  WHERE p.x <Length(combs)-length(replace(combs,',',''))+1)) ;


update export_art_comp c SET doublons='N' ;
update export_art_comp c SET doublons='O' WHERE EXISTS (
                                           SELECT * FROM export_art_comp_doublons WHERE comb="ART_COMP.COMBSTO_ID")  ;
                                              --AND "ART_COMP.COMBSTO_ID" NOT IN (1965713,2304,1857827,1857279,1938674,394769,1474005,1466295,964184)   ;

update export_art_comp c SET variantmanque='N' ;
update export_art_comp c SET variantmanque='O' WHERE EXISTS (
                                           SELECT * FROM export_art_comp_variant_prblm WHERE combsto_id="ART_COMP.COMBSTO_ID")  ;
                                              --AND "ART_COMP.COMBSTO_ID" NOT IN (1965713,2304,1857827,1857279,1938674,394769,1474005,1466295,964184)   ;

commit;

/*cr\ufffdation de la table des combinaison d\ufffdj\ufffd existante dans l'ERP*/
DROP TABLE  export_art_comp_actuel ;
CREATE TABLE export_art_comp_actuel AS(
SELECT combsto_id,1 rang,&erp.spe_listevarlib( 'STO',a.combsto_id) listevar_lib
FROM &erp.vrt_combsto a );

/*export des combsto existante mais diff\ufffdrentes en valeur de variantes*/
--DELETE FROM &erp.art_prix p WHERE EXISTS ( SELECT 1 FROM gen_artcompdiff g  WHERE "ART_COMP.COMBSTO_ID"=p.combsto_id)
DROP TABLE gen_artcompdiff ;
CREATE TABLE gen_artcompdiff AS
  (SELECT "ART_COMP.GENE_COD","ART_COMP.GENE_IND","ART_COMP.COMBSTO_ID" ,LISTEVAR_LIB FROM  export_art_comp c
   WHERE doublons='N' AND  variantmanque ='N'
     AND EXISTS (SELECT 1 FROM export_art_comp_actuel cc WHERE "ART_COMP.COMBSTO_ID"=cc.combsto_id)
  minus
  SELECT (SELECT gene_cod FROM &erp.vrt_combsto v WHERE v.combsto_id=cc.combsto_id) gene_cod,
         (SELECT gene_ind FROM &erp.vrt_combsto v WHERE v.combsto_id=cc.combsto_id) gene_ind,
          combsto_id,listevar_lib FROM export_art_comp_actuel cc WHERE EXISTS (SELECT 1 FROM export_art_comp c WHERE "ART_COMP.COMBSTO_ID"=cc.combsto_id)
  )  ;


--SELECT * FROM gen_artcompdiff, &erp.art_comp c where c.combsto_id= "ART_COMP.COMBSTO_ID" and c.critlibre2_val='COMPREF' IN (1439155 ,1456163)
--in (1882066)--,3513,992168,992169);
/*SELECT  "ART_COMP.GENE_COD",
          "ART_COMP.GENE_IND",
          "ART_COMP.COMBSTO_ID",
          "LISTEVAR_LIB" || chr(13) ||chr(10)  ||  &erp.svarliste( 'STO',"ART_COMP.COMBSTO_ID")
FROM gen_artcompdiff d where "ART_COMP.COMBSTO_ID" in (1882066)--,3513,992168,992169); */


/*SELECT * FROM erp_mli2.vrt_combstoval v, erp_mli2.vrt_combattval va WHERE va.combattval_num=v.COMBATTVAL_NUM
and v.combsto_id =1882066

SELECT ga.gene_cod,ga.gene_ind,ga.combinaison_rang,c.combsto_id,v.COMBSTOVAL_RANG,a.vrt_cod,a.att_cod
FROM erp_mli2.art_genechapatt ga, erp_mli2.vrt_combsto c, erp_mli2.vrt_combstoval v, erp_mli2.vrt_combattval a
WHERE c.gene_cod=ga.gene_cod AND c.gene_ind=ga.gene_ind
  AND v.combsto_id=c.combsto_id
  AND a.combattval_num = v.combattval_num
  AND a.VRT_COD = ga.vrt_cod
  AND a.ATT_COD =ga.vrtattatt_cod
  AND ga.combinaison_rang != v.COMBSTOVAL_RANG
  AND EXISTS (SELECT 1 FROM gen_artcompdiff g WHERE "ART_COMP.COMBSTO_ID"=c.combsto_id)
  --AND c.combsto_id IN (1439155 ,1456163)
ORDER BY c.gene_cod,c.combsto_id,ga.COMBINAISON_RANG*/

/*CREATE TABLE sav_art_prix_supp AS (SELECT * FROM erp_mli2.art_prix p
  WHERE EXISTS (SELECT * FROM gen_artcompdiff where "ART_COMP.COMBSTO_ID"=p.combsto_id))

DELETE FROM erp_mli2.art_prix p
  WHERE EXISTS (SELECT * FROM gen_artcompdiff where "ART_COMP.COMBSTO_ID"=p.combsto_id)

INSERT INTO erp_mli2.art_prix (SELECT * FROM sav_art_prix_supp s WHERE EXISTS (SELECT 1 FROM erp_mli2.art_comp c WHERE c.combsto_id=s.combsto_id))
*/

/*export des combsto qui n'existent pas encore dans l'erp*/
DROP TABLE gen_artcompmanq ;
CREATE TABLE gen_artcompmanq AS (
 SELECT "ART_COMP.GENE_COD","ART_COMP.GENE_IND","ART_COMP.COMBSTO_ID",LISTEVAR_LIB
 FROM  export_art_comp c
 WHERE NOT EXISTS (SELECT * FROM export_art_comp_actuel cc WHERE "ART_COMP.COMBSTO_ID"=cc.combsto_id)
   AND doublons ='N' AND  variantmanque ='N'
   --AND  "ART_COMP.COMBSTO_ID" =4130

 ) ;

--SELECT * FROM gen_artcompmanq where LISTEVAR_LIB not like '%+DM1+%' and "ART_COMP.COMBSTO_ID"=2554;
--SELECT * FROM export_art_comp WHERE "ART_COMP.COMBSTO_ID" IN (993014 ,1439155,992178,992169,992151,18738)


 SELECT 'REPRISE/ART_COMP_MANQ_sDM1.TXT' outname,g.* FROM gen_artcompmanq g where LISTEVAR_LIB not like '%+DM1+%'
 AND NOT  EXISTS (SELECT 1 FROM erp_mli2.art_comp c WHERE c.combsto_id="ART_COMP.COMBSTO_ID") ;

 SELECT 'REPRISE/ART_COMP_DIFF_sDM1.TXT' outname,d.* FROM gen_artcompdiff d where LISTEVAR_LIB not like '%+DM1+%' ;

 SELECT 'REPRISE/ART_COMP_MANQ_aDM1.TXT' outname,g.* FROM gen_artcompmanq g where LISTEVAR_LIB  like '%+DM1+%'
 AND NOT  EXISTS (SELECT 1 FROM erp_mli2.art_comp c WHERE c.combsto_id="ART_COMP.COMBSTO_ID") ;

 SELECT 'REPRISE/ART_COMP_DIFF_aDM1.TXT' outname,d.* FROM gen_artcompdiff d where LISTEVAR_LIB  like '%+DM1+%' ;

 select 'REPRISE/ART_COMP.TXT' outname,f.* from (
  SELECT g.* FROM gen_artcompmanq g where LISTEVAR_LIB not like '%+DM1+%'
 AND NOT  EXISTS (SELECT 1 FROM erp_mli2.art_comp c WHERE c.combsto_id="ART_COMP.COMBSTO_ID")
union all
 SELECT d.* FROM gen_artcompdiff d where LISTEVAR_LIB not like '%+DM1+%'
union all
 SELECT g.* FROM gen_artcompmanq g where LISTEVAR_LIB  like '%+DM1+%'
 AND NOT  EXISTS (SELECT 1 FROM erp_mli2.art_comp c WHERE c.combsto_id="ART_COMP.COMBSTO_ID")
union all
 SELECT d.* FROM gen_artcompdiff d where LISTEVAR_LIB  like '%+DM1+%'
 ) f

